# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html
import openpyxl as openpyxl
import pymysql as pymysql
# useful for handling different item types with a single interface
from itemadapter import ItemAdapter


class MysqlPipline:
    def __init__(self):
        self.connect = pymysql.connect(host='8.210.151.80', port=1003,
                                       user='root', password='123456',
                                       database='sipder', charset='utf8mb4')
        self.cursor = self.connect.cursor()
        self.data = []
        self.item = None

    def open_spider(self, spider):
        print('MysqlPipline:', '开始抓取。。。。！')


    def close_spider(self, spider):
        if len(self.data) > 0:
            self._write_mysql_()
        self.connect.close()


    def process_item(self, item, spider):
        title = item.get('title', '')
        rank = item.get('rank', '')
        subject = item.get('subject', '')
        duration = item.get('duration', '')
        introduce = item.get('introduce', '')
        self.data.append((title, rank, subject, duration, introduce))
        if len(self.data) == 100:
            self._write_mysql_()
            self.data.clear()
        return item


    def _write_mysql_(self):
        for self.item in self.data:
            print('data:', self.item)
            self.cursor.execute(
                'insert into movie (title, rating, subject, duration, introduce) values (%s, %s, %s, %s, %s)',
                self.item
            )
            self.connect.commit()


class ExcelPipeline:
    # 创建excel表
    def __init__(self):
        self.wb = openpyxl.Workbook()
        self.ws = self.wb.active
        self.ws.title = 'Top250'
        self.ws.append(('标题', '评分', '主题', '时长', '介绍'))

    def open_spider(self, spider):
        print('ExcelPipeline:', '开始抓取。。。。！')


    def close_spider(self, spider):
        self.wb.save('电影数据.xlsx')

    # 拿到数据，提取对应的值，存入表中
    def process_item(self, item, spider):
        # title = item.get('title', '')
        # rank = item.get('rank', 0)
        # subject = item.get('subject', '')
        # duration = item.get('duration', '')
        # introduce = item.get('introduce', '')
        self.ws.append((item['title'], item['rank'], item['subject'], item['duration'], item['introduce']))
        return item
